Database requirements for upgrading

For certain StarTeam Server upgrades, you may also need to upgrade or migrate your database to a version supported by the new version of the StarTeam Server. For more information about which databases are supported by StarTeam Server, see Databases.

Backup types and recovery models

This section describes the various backup types and recovery models for the Microsoft SQL Server. For server configuration online backups, it is essential to perform the full database and transaction log backups.

Microsoft SQL Server supports the following types of backups:

Backup type	Description
Full	A Full database backup creates a duplicate of the data that is in the database. This is a single operation, usually scheduled at regular intervals. Full database backups are self-contained. Full backups provide a snapshot of the database. Most of the recovery options require a full backup to be present. Note: This is the recommend backup type.
Differential	A Differential database backup records only the data that changed since the last database backup. Frequent differential backups are recommended to reduce backup times. Making frequent backups decreases the risk of losing data. A differential backup requires a prior full backup. Differential backups restore the data that they contain to the database. Differential backups cannot be used to recover the database to a point in time. The availability of a differential backup minimizes the time it takes to roll forward transaction log backups when restoring a database.
Transaction Log	A Transaction Log backup includes all the transactions since the last transaction log backup. With transaction log backups, you can recover the database to a specific point in time or to the point of failure. Transaction log backups enable recovery up to the last committed transaction. When restoring a transaction log backup, Microsoft SQL Server rolls forward all the changes recorded in the transaction log. When Microsoft SQL Server reaches the end of the transaction log, it has recreated the exact state of the database at the time of the backup operation. If the database is recovered, Microsoft SQL Server then rolls back all transactions that were incomplete when the backup operation started. Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease the risk of losing data. For high volume Online Transaction Processing (OLTP) environments, it is desirable to create transaction log backups more frequently. The following guidelines apply: Transaction log backups can only be used with the Full and Bulk-logged recovery models. The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running. Never backup a transaction log before a database backup is created because the transaction log contains the changes made to the database after the last backup was created. Never truncate the transaction log manually because it breaks the backup chain. If a transaction log has been truncated, take a full database backup to start a new backup chain.
File or File Group	A File or File Group backup, backs up individual data files (or the files in the file group). The files in a database can be restored individually. A file-based recovery model increases the speed of recovery by allowing you to restore only the damaged files without restoring the rest of the database. For example, suppose a database is comprised of several files located physically on different disks and one disk fails. Only the file on the failed disk needs to be restored and rebuilt using the transaction log backup. File backup and restore operations must be used in conjunction with transaction log backups. For this reason, file backups can only be used with the Full and Bulk-logged recovery models.

The entire database can be recreated from a database backup in a single step by restoring the database. The restore process overwrites the existing database or, if it does not exist, creates the database . The restored database will match the state of the database at the time the backup completed, not including any uncommitted transactions. Uncommitted transactions are rolled back when the database is recovered.

Based on the resource requirements, the DBA can also choose the recovery model for the database. The recovery model balances logging overhead against the criticality of fully recovering the data.

The recovery models supported by Microsoft SQL Server are:

Recovery model	Description
Full	The data is critical and must be recoverable to the point of failure. All data modifications are logged. All Microsoft SQL Server recovery options are available.
Bulk-logged	Certain bulk operations, such as bulk copy operations, `SELECT INTO`, and text processing, can be replayed if necessary, so these operations are not fully logged. You can recover only to the end the last database or log backup.
Simple	All data modifications made since the last backup are not available. This type of recovery scenario has the lowest logging overhead, but cannot recover past the end of the last backup.

Recovering to a point-in-time (for example, a time before unwanted data was entered) requires either the Full or Bulk-logged recovery models.

Database backup recommendations

For a Microsoft SQL Server database, the backup recommendations are:

Use the Full recovery model.
Perform a Full database backup once every day. For database sizes greater than 3 GB, it is usually acceptable to perform a full backup on alternate days. If you perform full backups on alternate days, we recommend that you create daily differential backups.
Create daily transaction log backups after the completion of the full or differential backup. In addition to this, schedule a transaction log backup every 4 hours. Never truncate a transaction log manually.
In case of a disaster, create a backup of the currently active transaction log. If active transaction log backup is not available (for example, because a media failure for the drive hosting the transaction logs and drive is not being mirrored), the database cannot be recovered past the last available transaction log backup. This would hamper a point-in-time recovery beyond the last available transaction log backup.

Orphaned users

When a database user for a corresponding Microsoft SQL Server login is undefined or incorrectly defined, Microsoft SQL Server does not allow the user to log on to that instance. Such a user is said to be an orphaned user of the database instance. A database user can become orphaned if the corresponding Microsoft SQL Server user name is dropped. Most likely, a database user becomes orphaned after a database is restored or attached to a different instance of Microsoft SQL Server. Orphans occur if the database user is mapped to a security ID that is not present in the new server instance.

Use Microsoft SQL Server Management Studio or any other tool that executes SQL statements to address all orphaned users before starting or upgrading the StarTeam Server configuration. Use either the system administrator user name (sa) or windows authentication (under an administrative user account) to connect to the database and change the database context for the SQL console to the StarTeam database.

To address orphaned users:

Open a SQL console such as Microsoft SQL Server Management Studio or Enterprise Manager.
Open a new query.
Enter the following command to display the user names of all orphaned users: sp_change_users_login 'REPORT'.
Enter the following to address the orphaned database owner (dbo). The database user is always orphaned when changing servers:
```
sp_addlogin User Name , Password
go
sp_changedbowner User Name
go
sp_defaultdb User Name , Database Name
```
User Name is the appropriate user name, Password is the appropriate password for the StarTeam Server, and Database Name is the StarTeam Server Database.
For all other users who are returned as orphans, repeat the following commands for each of the orphaned users:
```
sp_addlogin User Name, Password
go
EXEC sp_change_users_login 'Update_One', 'Orphaned User Name', 'New User Name'
```
Where the Orphaned User Name is the orphaned user from the previous server and the New User Name is the new login created in the previous step.
Enter the following command: sp_change_users_login 'REPORT'. This command should not return any rows.

For more information, refer to the Troubleshooting Orphaned Users section in the Microsoft SQL Server documentation.

Granting Oracle privileges

If your StarTeam Server configuration uses an Oracle schema user that was not created automatically by StarTeam Server, you should check it for all the privileges listed below. These privileges ensure that StarTeam Server can create labels and perform other operations successfully and should be explicitly defined rather than relegated to a role.

Create procedure
Create sequence
Create session
Create table
Create trigger
Create type
Create view
Unlimited tablespace
Create any context
Drop any context

The following example illustrates how to explicitly define Oracle privileges, using the Create type privilege.

To explicitly define the Create type privilege:

Log on to the database as system user.
Execute the following SQL statement: grant create type to schema_user, where schema_user is the name of the Oracle schema user.

Setting the database compatibility level

For Microsoft SQL Server 2008, you will need to change the database compatibility level to 100 for all existing databases that will be upgraded from an earlier version of Microsoft SQL Server. All new configurations created against Microsoft SQL Server 2008 databases will have the correct value.

To set the database compatibility level:

Log on to the database instance.

Execute the command:

sp_dbcmptlevel @dbname = name,@new_cmptlevel = version]